/*
 *                    BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 *
 * Created on 15.04.2004
 * @author Andreas Prlic
 *
 */

package org.biojava.services.das.registry;

//import java.util.Arrays                       ;

import java.util.ArrayList;
import java.util.Date;
import java.util.Map;
import java.util.List;
import java.util.Iterator;
import java.util.Set;

//database stuff
import java.sql.*;

import javax.sql.DataSource;

//for mail
import javax.mail.*;
import javax.mail.internet.*;
import java.util.Properties;
import java.util.logging.Logger;
import java.io.Serializable;
import java.net.MalformedURLException;
import java.net.URL;
import javax.naming.Context;
import javax.naming.InitialContext;

import org.biojava.dasobert.das.Capabilities;
import org.biojava.dasobert.das.DasSpec;
import org.biojava.dasobert.dasregistry.Das1Source;
import org.biojava.dasobert.dasregistry.Das1_6Validator;
import org.biojava.dasobert.dasregistry.DasCoordinateSystem;
import org.biojava.dasobert.dasregistry.DasSource;
import org.biojava.dasobert.dasregistry.Das1Validator;
import org.biojava.dasobert.dasregistry.DasValidationResult;

import org.biojava.services.das.dao.*;
import org.biojava.services.das.dao.ServerManager;
import org.biojava.services.das2.Das2Mirror;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.io.FileSystemResource;

import uk.ac.sanger.dasregistry.timer.Mirroring;

import dasregistry.validation.Validator;

/**
 * a standard DasRegistry service reads configuration from dasregistry.cfg file
 */

public class DasRegistrySql implements DasRegistry, NewDasSourceListener,
		Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public static Logger logger = Logger
			.getLogger("org.biojava.services.das.registry");

	// this are the capabilities supported by the registry ...
	// public static final String[] DAS_CAPABILITIES = .DAS_CAPABILITIES;

	// 1000 milli sec * 60 sec * 60 min = 1 hour = 3600000 milli sec;
	public static long REMINDER_TIMEDIFF = 3600000;

	/** timeout when making DAS requests... */
	public static int TIMEOUT = 10000;

	// make sure this is in sync with the mysql max length for this column
	public static final int MAX_NICKNAME_LENGTH = 50;

	/** the character that separates the prefix from the number */
	public static String PREFIXSPLIT = "_";

	// return errors:
	public static final int DATABASE_ERROR = 4;
	public static final int DUPLICATE_ENTRY_ERROR = 2;
	public static final int COORDINATE_SYSTEM_INVALID = 9;
	public static final int WRONG_PASSKEY = 2;

	public static final int FIRST_WARNING = 2;// 2 days for first warning of
	// completely invalid server
	public static final int ARCHIVE_TIME = 60;
	// prefix for the unique ID of this server.
	// eg. public das sources start with DASSOURCE:
	// whereas local onces could start with SANGERLOCAL:
	String prefix;

	// a flag if this registry should provide a cache of the parent registry.
	// if true a timertask will retrieve the parent registry das sources and if
	// found,
	// update the local copy.
	boolean cacheParent;

	// how often should parent be contacted and updated?
	// e.g. 1000 milli sec * 60 sec * 60 min = 3600000 = 1 / hour
	int contactParentFrequency;

	int autoValidationFrequency;

	String validationMessage;

	// TODO: move this to config file!
	public final static String DATASOURCE_NAME = "jdbc/mysql";

	// protected RegistryPooledDataSource dataSourcePool;
	DataSource dataSource;
	Mirroring mirror;

	// public void setDataSource(DataSource dataSource) {
	// this.dataSource = dataSource;
	// }

	public Mirroring getMirror() {
		return mirror;
	}

	public void setMirror(Mirroring mirror) {
		this.mirror = mirror;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	Map configuration;

	long config_timediff;

	// listeners if a new data source is found
	List newDataSourceListeners;

	// the manager that takes care of (most of) the SQL queries
	DasSourceManager dasSourceManager;

	public DasSourceManager getDasSourceManager() {
		return dasSourceManager;
	}

	public void setDasSourceManager(DasSourceManager dasSourceManager) {
		this.dasSourceManager = dasSourceManager;
	}

	String relaxNgPath1_5 = null;
	String relaxNgPath1_6 = null;

	ServerManager serverManager;
	UserManager userManager;

	public UserManager getUserManager() {
		return userManager;
	}

	public void setUserManager(UserManager userManager) {
		this.userManager = userManager;
	}

	public ServerManager getServerManager() {
		return serverManager;
	}

	public void setServerManager(ServerManager serverManager) {
		this.serverManager = serverManager;
	}

	DasUserDAO dasUserDAO;
	DasCoordinateManagerSpring coordManager;
	// DasSourceManagerSpring sourceManager;

	static int dasregistrySqlInstances = 0;

	/**
	 * default constructor;
	 */
	public DasRegistrySql() {
		super();
		logger.finest("number of registry instances="
				+ dasregistrySqlInstances);
		dasregistrySqlInstances++;
		
		RegistryConfiguration rconfig = new RegistryConfiguration();
		// rconfig is set by the outside via Spring
		configuration = rconfig.getConfiguration();
		

		try {


			prefix = (String) configuration.get("idPrefix");

			// test validity of prefix:
			if (prefix == null) {
				throw new RegistryException(
						"Please configure idPrefix in config, it must not be null!");
			}
			// remove eventual trailing ":" ;
			prefix = prefix.replaceAll(PREFIXSPLIT, "");

			if (prefix.length() > 30) {
				throw new RegistryException(
						"Come on - you are using an extremly long idPrefix for your local DAS registry! please use a shorter string");
			}

			try {
				String td = (String) configuration.get("mailfrequency");

				config_timediff = Long.parseLong(td);

			} catch (Exception e) {
				e.printStackTrace();
				config_timediff = REMINDER_TIMEDIFF;
			}

			try {
				logger.fine("setting relaxng path in DASRegistrySql");
				relaxNgPath1_5 = (String) configuration.get("relaxNgPath1_5");
				relaxNgPath1_6 = (String) configuration.get("relaxNgPath1_6");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			validationMessage = "";
			System.setProperty("sun.net.client.defaultConnectTimeout", ""
					+ TIMEOUT);
			System.setProperty("sun.net.client.defaultReadTimeout", ""
					+ TIMEOUT);

			// ///////////////////////
			// initCaching();
			// set cahcing properties
			Map cacheMap = (Map) configuration.get("cacheParent");
			if (cacheMap == null) {
				System.out
						.println("no cacheParent entry in config, caching disabled");
				cacheParent = false;
				return;

			}
			// set caching data
			String cacheParentstr = (String) cacheMap.get("value");

			cacheParent = false;
			if (cacheParentstr != null) {
				if (cacheParentstr.equals("true")) {
					cacheParent = true;
				}
			} else {
				System.out
						.println("cacheParent >value< not set in config - assuming "
								+ cacheParent);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}

	}

	public DasUserDAO getDasUserDao() {
		return dasUserDAO;
	}

	public void setDasUserDao(DasUserDAO userDao) {
		this.dasUserDAO = userDao;
	}

	public String getRegistryPrefix() {
		return prefix;
	}

	/**
	 * The event listener method for the interface NewDataSourceListener return
	 * passkey
	 */
	public int newDasSourceFound(DasSource ds) {
		Connection conn = null;
		int status = 13;
		try {
			conn = dataSource.getConnection();
			int retcode = dasSourceManager.storeInDb(conn, ds, prefix);

			status = retcode;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			status = DATABASE_ERROR;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		// return that a db error has occured ...
		return status;
	}

	/*
	 * DasSource ds must have UniqueId property set returns : 1 - update o.k. 3
	 * - server failed validataion test 4 - database error - could not add
	 * server to database 5 - unknown capability 6 - adminemail not a valid
	 * email address 7 - dasurl not valid 8 - description not valid 9 -
	 * coordSystem not valid 10 - capabilities not valid 11 - testCode not
	 * found. 12 - nickname already in use / invalid nickname 13 - unknown error
	 * occured...
	 */
	public int updateSource(DasSource ds) {
		Connection conn = null;
		Connection connForTypes = null;
		int status = 13;

		int UNK_AUTO_ID = DasSourceManager.UNKNOWN_AUTO_ID;
		int auto_id = UNK_AUTO_ID;
		try {
			auto_id = dasSourceManager.getAutoIdForURL(ds.getUrl());

			conn = dataSource.getConnection();

			if (auto_id == UNK_AUTO_ID) {

				try {
					if (hasLocalPrefix(ds.getId())) {
						String[] spl = ds.getId().split(PREFIXSPLIT);
						if (spl.length == 2) {
							try {
								auto_id = Integer.parseInt(spl[1]);
							} catch (NumberFormatException ex) {
								ex.printStackTrace();
							}
						}
					}
				} catch (RegistryException rex) {
					rex.printStackTrace();
				}
			}

			if (auto_id != UNK_AUTO_ID) {
				boolean success = dasSourceManager.updateInDb(conn, auto_id,
						ds, prefix);

				userManager.addDasSource2Owner(auto_id);

				if (success) {
					status = 1;

				}

			} else {
				System.err
						.println("could not find auto_id for requested DasSource "
								+ ds.getUrl());
			}
		} catch (SQLException e) {
			// System.out.println(e.getMessage());
			e.printStackTrace();
			status = DATABASE_ERROR;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		pingLastModified();

		return status;

	}

	/*
	 * DasSource ds must have UniqueId property set returns : 1 - update o.k. 3
	 * - server failed validataion test 4 - database error - could not add
	 * server to database 5 - unknown capability 6 - adminemail not a valid
	 * email address 7 - dasurl not valid 8 - description not valid 9 -
	 * coordSystem not valid 10 - capabilities not valid 11 - testCode not
	 * found. 12 - nickname already in use / invalid nickname 13 - unknown error
	 * occured...
	 */
	public int updateValidCapabilities(DasSource ds) {

		Connection conn = null;
		Connection connForTypes = null;
		int status = 13;

		try {

			conn = dataSource.getConnection();
			connForTypes = dataSource.getConnection();
			int UNK_AUTO_ID = DasSourceManager.UNKNOWN_AUTO_ID;
			int auto_id = UNK_AUTO_ID;
			try {
				auto_id = dasSourceManager.getAutoIdForURL(ds.getUrl());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if (connForTypes != null) {
					try {
						connForTypes.close();
					} catch (SQLException ex) {
						ex.printStackTrace();
					}
				}
			}

			if (auto_id == UNK_AUTO_ID) {
				logger.info("trying to validate a data source that is not known - something wrong with AutoValidation");
				// try {
				// if (hasLocalPrefix(ds.getId())) {
				// String[] spl = ds.getId().split(PREFIXSPLIT);
				// if (spl.length == 2) {
				// try {
				// auto_id = Integer.parseInt(spl[1]);
				// } catch (NumberFormatException ex) {
				// ex.printStackTrace();
				// }
				// }
				// }
				// } catch (RegistryException rex) {
				// rex.printStackTrace();
				// }
			}

			if (auto_id != UNK_AUTO_ID) {
				boolean success = dasSourceManager.updateValidationStatusInDb(
						conn, auto_id, ds, prefix);

				// UserManager um = new UserManager();
				// um.addDasSource2Owner(auto_id);

				if (success)
					status = 1;

			} else {
				System.err
						.println("could not find auto_id for requested DasSource "
								+ ds.getUrl());
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
			status = DATABASE_ERROR;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		// pingLastModified();

		return status;

	}

	/** remove all html tags and string characters from string */
	private String protectString(String str) {

		str = str.replaceAll("<", "");
		str = str.replaceAll(">", "");
		str = str.replaceAll("\"", "");
		str = str.replaceAll("'", "");
		return str;
	}

	// TODO: move all return codes into a class that only provides the static
	// return codes

	/*
	 * (non-Javadoc)
	 * 
	 * @see DasRegistry#registerService(java.lang.String, java.lang.String,
	 * java.lang.String)
	 */
	public int registerService(String url, String nickname, String adminemail,
			String description, String[] cs_types, List<String> capabilities,
			String[] labels, String helperurl, String[] testCodes,
			DasSpec spec, String queryUri) {

		boolean ignoreValidation = false;

		return registerService(url, nickname, adminemail, description,
				cs_types, capabilities, labels, helperurl, testCodes,
				ignoreValidation, spec);
	}

	/**
	 * the call of this method is protected through the web site. only admins
	 * are allowed to set ignoreValidation to true. used by registerMe method in
	 * registryBean
	 * 
	 * @param url
	 * @param nickname
	 * @param adminemail
	 * @param description
	 * @param cs_types
	 * @param capabilities
	 * @param labels
	 * @param helperurl
	 * @param testCodes
	 * @param ignoreValidation
	 * @param spec
	 *            TODO
	 * @return status
	 */
	public int registerService(String url, String nickname, String adminemail,
			String description, String[] cs_types, List<String> capabilities,
			String[] labels, String helperurl, String[] testCodes,
			boolean ignoreValidation, DasSpec spec) {

		/*
		 * return: in which provides an Exitcode. 1 - service has been
		 * registered successfully 0 - unknown error occured 2 - server already
		 * in database, not registering a second time 3 - server failed
		 * validation test 4 - database error - could not add server to database
		 * 5 - unknown capability 6 - adminemail not a valid email address 7 -
		 * dasurl not valid 8 - description not valid 9 - coordSystem not valid
		 * 10 - capabilities not valid 11 - testCode not found. 12 - nickname
		 * already in use / invalid nickname an int > 1000 ; a random number
		 * which serves as key required to remove a server from the registry
		 */

		DasSourceTester tester = new DasSourceTester(this);

		String validateOntology = "false";
		int status = tester.testArguments(url, nickname, adminemail,
				description, cs_types, capabilities, labels, helperurl,
				testCodes, ignoreValidation, validateOntology);

		if (status != 1) {
			return status;
		}

		// protect the strings
		url = protectString(url);
		description = protectString(description);

		for (int i = 0; i < cs_types.length; i++) {
			String cs = cs_types[i];
			cs = protectString(cs);
			cs_types[i] = cs;
		}

		boolean continueFlag = true;

		Connection conn = null;
		try {
			// debugg
			debug(dataSource);
			conn = dataSource.getConnection();

			boolean knownnick = dasSourceManager
					.isKnownNickname(conn, nickname);
			if (knownnick) {
				continueFlag = false;
				status = 12;
				return status;
			}

			DasCoordinateSystem[] supportedCS = null;
			if (continueFlag) {

				// / test the coordinate systems ...

				// test if the corresponding coordinate systems are known ..

				supportedCS = getCoordSysByString(conn, cs_types, testCodes);

				// at least one coordiante system needs to be supported ...
				// all provide cs_types have to exist ...
				if ((supportedCS == null) || (supportedCS.length < 1)
						|| (supportedCS.length != cs_types.length)) {
					status = COORDINATE_SYSTEM_INVALID;
					continueFlag = false;
				}
			}

			boolean ontology = false;
			if (validateOntology.equals("true")) {
				ontology = true;
			}

			Validator validator = new Validator();
			DasValidationResult result = validator.validate(spec, url,
					capabilities, supportedCS, ontology, true, this, null);

			
				if (!result.isOverallValid()) {
					return 3;
				}
			
			// DasRegistry.SPEC1_5,url, testCode, caps,ontology

			if (continueFlag) {
				// open connection to local database
				// check if service is already available
				boolean found = dasSourceManager.isInDatabase(conn, url);
				if (found) {
					status = DUPLICATE_ENTRY_ERROR;
					continueFlag = false;
				}
			}

			if (continueFlag) {
				int passkey = 1;
				DasSource ds = new Das1Source();
				ds.setUrl(url);
				ds.setNickname(nickname);
				ds.setAdminemail(adminemail);
				ds.setDescription(description);
				ds.setCoordinateSystem(supportedCS);
				System.out.println(capabilities);
				ds.setCapabilities(capabilities);
				// as this is a newly registered source it should have it's
				// capabilities listed should be valid
				// until proven otherwise by autovalidation so we
				// setvalidCapabilities the same as those tested
				ds.setValidCapabilities(result.getValidCaps());
				ds.setLabels(labels);
				ds.setHelperurl(helperurl);

				// ds.setLocal(true);
				ds.setSpecification(spec.toString());

				passkey = dasSourceManager.storeInDb(conn, ds, prefix);

				// return the passkey
				// everything worked o.k.

				status = passkey;
				System.out.println("successfully registered!");
			}

			pingLastModified();

			debug(dataSource);

		} catch (SQLException e) {
			e.printStackTrace();
			status = DATABASE_ERROR;

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return status;
	}

	public void pingLastModified() {
		Connection conn = null;
		try {
			// debugg
			debug(dataSource);
			conn = dataSource.getConnection();

			dasSourceManager.pingLastModified(conn);

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public java.util.Date getlastModified() {
		Connection conn = null;
		Date d = new Date(0);
		try {
			// debugg
			debug(dataSource);
			conn = dataSource.getConnection();

			d = dasSourceManager.getLastModified(conn);
			// System.out.println("Debugging with getlast modified set to always true");
			// for debugging set date modified to now
			// d=new Date();

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return d;

	}

	// debugg
	private void debug(DataSource d) {
		// System.out.println("debug -skipping");
		if (d == null) {
			logger.severe("DataSource = null!! - this should not happend... there will be problems.");
			logger.severe("Please check if there is a problem with the db connection configuration.");

			return;
		}

		// ObjectPool pool = dataSourcePool.getObjectPool();
		// logger.info("got connection " + pool.getNumActive() + " (active) "+
		// pool.getNumIdle() + " (idle)");

	}

	public DasCoordinateSystem[] getCoordSysByString(Connection conn,
			String[] cs_types, String[] testCodes) throws SQLException {

		DasCoordinateSystem[] coords = getLocalCoordinateSystems(conn);

		List supportedCoordinateSys = new ArrayList();
		for (int i = 0; i < cs_types.length; i++) {
			String cs = cs_types[i];
			String testCode = testCodes[i];
			// map cs to DasCoordinateSystem

			for (int j = 0; j < coords.length; j++) {
				DasCoordinateSystem dcs = coords[j];

				if (dcs.toString().equals(cs)) {
					dcs.setTestCode(testCode);
					supportedCoordinateSys.add(dcs);
					break;
				}
			}
		}
		return (DasCoordinateSystem[]) supportedCoordinateSys
				.toArray(new DasCoordinateSystem[supportedCoordinateSys.size()]);
	}

	/*
	 * private boolean isRegisterService(String[] capabs) { // test if the
	 * capabs contain sequence or structure List capabils =
	 * Arrays.asList(capabs);
	 * 
	 * if ( ( capabils.contains("sequence")) || (
	 * capabils.contains("structure")) ) { return true ; } return false ; }
	 */

	/**
	 * if config file contains element "parentregistry" return an instance of
	 * DasRegistryAxisClient that will use the "parent" DAS registry
	 * 
	 * @return paretn DasRegistry
	 */

	

	/** perform a keyword search to identify all matching DAS sources */
	public List<DasSource> keywordSearch(String keyword) {
		// logger.info("keyword search for " + keyword );

		logger.info("keyword search for " + keyword);
		List<DasSource> sources;
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			sources = dasSourceManager.keywordSearch(conn, keyword, prefix);

		} catch (SQLException e) {
			// System.err.println("could not get DB Connection ...");
			logger.warning("DasRegistrySql keywordSearch: could not open DB connection!"
					+ e.getMessage());
			e.printStackTrace();

			sources = new ArrayList();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return sources;
	}

	/** perform a keyword search to identify all matching DAS sources */
	public List<DasSource> typesSearch(String keyword) {
		// logger.info("keyword search for " + keyword );

		logger.info("keyword search for " + keyword);
		List<DasSource> sources;
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			sources = dasSourceManager.typesSearch(conn, keyword, prefix);

		} catch (SQLException e) {
			// System.err.println("could not get DB Connection ...");
			logger.warning("DasRegistrySql keywordSearch: could not open DB connection!"
					+ e.getMessage());
			e.printStackTrace();

			sources = new ArrayList();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return sources;
	}

	/**
	 * find DasSources that are owned by a particular user
	 * 
	 * @param openid
	 * @return all DAsSources that are owned by user with the provided OpenID
	 */
	public List<DasSource> getServicesForEmail(String openid) {
		List<DasSource> sources = new ArrayList<DasSource>();

		Connection conn = null;
		try {

			conn = dataSource.getConnection();

			sources = dasSourceManager.getDasSourcesByOpenID(conn, openid,
					prefix);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
		}

		return sources;
	}

	// /** set the owner of a DAS source
	// *
	// * @param url
	// * @param openid
	// */
	// public void setOwner(String url, String openid){
	// Connection conn = null ;
	// try {

	// conn = dataSource.getConnection() ;

	// dao.setDasSourceOwner(conn,url,openid);

	// } catch (SQLException e) {
	// e.printStackTrace();
	// } finally {
	// if ( conn != null){
	// try {
	// conn.close();
	// } catch (SQLException ex){
	// ex.printStackTrace();
	// }
	// }
	// }
	// }

	public DasSource[] getDasSourcesForProject(int project_id) {

		DasSource[] sources = new DasSource[0];
		Connection conn = null;
		try {

			conn = dataSource.getConnection();
			sources = dasSourceManager.getDasSourcesByProjectID(conn,
					project_id, prefix);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
		}

		return sources;
	}

	/**
	 * return list of available DAS sources if config file contains element
	 * "parentregistry", contact to a second registry to retreive das souces
	 * from there.
	 * 
	 * @return and array of avialble DasSources
	 */

	public List<DasSource> listServices() {

		List<DasSource> sources = new ArrayList<DasSource>();
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			// System.err.println("could not get DB Connection ...");
			logger.warning("DasRegistrySql listServices: could not open DB connection!"
					+ e.getMessage());
			e.printStackTrace();
		}
		boolean allOk = true;
		try {
			sources = dasSourceManager.getAllSources(conn, prefix);
			return sources;

		} catch (Exception e) {
			e.printStackTrace();
			allOk = false;
		} finally {
			try {
				conn.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return sources;
		// if (allOk)
		// return sources;
		// else {
		// return new DasSource[0];
		// }
	}

	public List<DasSource> listArchivedSources() {

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			// System.err.println("could not get DB Connection ...");
			logger.warning("DasRegistrySql listServices: could not open DB connection!"
					+ e.getMessage());
			e.printStackTrace();

		}
		List sources = null;
		boolean allOk = true;
		try {

			sources = dasSourceManager.getAllArchvedSources(conn, prefix);

		} catch (Exception e) {
			e.printStackTrace();
			allOk = false;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}

		}
		return sources;
	}

	public List<DasSource> listServicesForArchiving() {

		System.out.println("DasRegistrySql listServices for archiving");

		List<DasSource> sources = new ArrayList<DasSource>();
		// ArrayList ret = new ArrayList() ;

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			// System.err.println("could not get DB Connection ...");
			logger.warning("DasRegistrySql listServices: could not open DB connection!"
					+ e.getMessage());
			e.printStackTrace();

			return sources;
		}

		boolean allOk = true;
		try {

			sources = dasSourceManager.getSourcesforArchiving(conn, prefix);

		} catch (Exception e) {
			e.printStackTrace();
			allOk = false;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}

		}
		return sources;

	}

	/** retrieve all available labels */
	public String[] getAllLabels() {

		String[] allLabels = new String[0];

		Connection conn = null;

		try {
			debug(dataSource);

			conn = dataSource.getConnection();

			allLabels = dasSourceManager.getAllLabels(conn);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return allLabels;

	}

	private DasCoordinateSystem[] getLocalCoordinateSystems(Connection conn)
			throws SQLException {
		System.out.println("DasRegistrySql: getLocalCoordinateSystems");

		List coordinateSystems = new ArrayList();

		DasCoordinateSystemCollector m = dasSourceManager
				.getAllCoordinateSystemsMap(conn, prefix);
		Set s = m.keySet();
		Iterator iter = s.iterator();
		while (iter.hasNext()) {
			Integer key = (Integer) iter.next();
			DasCoordinateSystem csname = (DasCoordinateSystem) m.get(key);
			coordinateSystems.add(csname);
			// System.out.println("coordinate system added to array: " +
			// csname);
		}

		return (DasCoordinateSystem[]) coordinateSystems
				.toArray(new DasCoordinateSystem[coordinateSystems.size()]);
	}

	private DasCoordinateSystem[] getLocalCoordinateSystemsByName(
			String startOfName, String prefix, Connection conn)
			throws SQLException {
		// System.out.println("DasRegistrySql: getLocalCoordinateSystems");

		List coordinateSystems = new ArrayList();

		DasCoordinateSystemCollector m = dasSourceManager.getCoordinatesByName(
				startOfName, prefix, conn);
		Set s = m.keySet();
		Iterator iter = s.iterator();
		while (iter.hasNext()) {
			Integer key = (Integer) iter.next();
			DasCoordinateSystem csname = (DasCoordinateSystem) m.get(key);
			coordinateSystems.add(csname);
			// System.out.println("coordinate system added to array: " +
			// csname);
		}

		return (DasCoordinateSystem[]) coordinateSystems
				.toArray(new DasCoordinateSystem[coordinateSystems.size()]);
	}

	public int getAutoID4DasSource(DasSource ds) {
		int auto_id = -1;
		Connection conn = null;
		try {

			conn = dataSource.getConnection();
			auto_id = dasSourceManager.getAutoIdForURL(ds.getUrl());

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
		}
		return auto_id;

	}

	public OrganismBean[] getAllOrganisms() {

		OrganismBean[] organisms = new OrganismBean[0];

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			organisms = dasSourceManager.getAllOrganism(conn);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		return organisms;
	}

	/** return all known CoordinateSystems */
	public List<DasCoordinateSystem> getAllCoordinateSystems() throws Exception {
		// System.out.println("in getAllCoordinateSystems in DasRegistrySql.java");
		ArrayList<DasCoordinateSystem> coords = new ArrayList();

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			DasCoordinateSystem[] localCoords = getLocalCoordinateSystems(conn);

			for (int l = 0; l < localCoords.length; l++) {
				coords.add(localCoords[l]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		return coords;
	}

	/**
	 * Creates one or more coordinate systems and their associated authorities.
	 * Does NOT create associated species' or categories, these are required. If
	 * the coordinate system exists, the ID is populated.
	 * 
	 * @param dcs
	 *            a List of coordinate systems to create
	 * @return a new List of coordinate systems representing those created
	 *         (which may more accurate species names etc)
	 * @throws java.lang.Exception
	 */
	public List<DasCoordinateSystem> createCoordinateSystems(
			List<DasCoordinateSystem> dcs) throws Exception {

		Connection conn = null;
		List<DasCoordinateSystem> createdDcs = new ArrayList<DasCoordinateSystem>(
				dcs.size());

		try {

			conn = dataSource.getConnection();
			for (int i = 0; i < dcs.size(); i++) {

				DasCoordinateSystem cs = dcs.get(i);

				// Basic tests for required fields:
				if (cs.getName() == null || cs.getName().equals("")) {
					throw new RegistryException(
							"Coordinate system name/authority is required");
				}
				if (cs.getCategory() == null || cs.getCategory().equals("")) {
					throw new RegistryException(
							"Coordinate system category/type is required");
				}

				// Create the coordinate system name (e.g. "NCBI_36") if
				// necessary
				int csNameId = dasSourceManager.getCoordAuto(conn,
						cs.getName(), cs.getVersion());
				if (csNameId < 1) {
					csNameId = dasSourceManager.createNewCsName(conn,
							cs.getName(), cs.getVersion());
				}

				int ncbiId = cs.getNCBITaxId();
				String orgName = cs.getOrganismName();
				// If the coordinate system is species specific (use the taxid
				// as the primary source):
				if (ncbiId > 0) {

					// Check the validity of the taxonomy ID
					String knownOrgName = dasSourceManager
							.getOrganismByNCBITaxId(conn, ncbiId);
					if (knownOrgName == null) {
						throw new RegistryException(
								"Unknown organism with taxonomy ID: " + ncbiId);
					}

				} else if (orgName != null && !orgName.equals("")) {
					ncbiId = dasSourceManager.getNCBITaxId(conn,
							cs.getOrganismName()); // Throws
					// exception
					// if
					// not
					// found,
					// but
					// test
					// just
					// in
					// case
					if (ncbiId < 1) {
						throw new RegistryException("Unknown organism: "
								+ cs.getOrganismName());
					}
				}
				// otherwise this coordinate system is not species-specific

				int categoryId = dasSourceManager.getCategoryAuto(conn,
						cs.getCategory());
				if (categoryId < 1) {
					throw new RegistryException(
							"Unknown coordinate system category: "
									+ cs.getCategory());
				}

				DasCoordinateSystem exists = dasSourceManager
						.getCoordinateSystem(conn, csNameId, categoryId, ncbiId);

				// test in here to see that it doensn't exist already then test
				// in the database
				List<DasCoordinateSystem> coords = this
						.getAllCoordinateSystems();
				cs.setNCBITaxId(ncbiId);

				System.out.println(cs);
				for (DasCoordinateSystem coord : coords) {
					if (cs.equals(coord)) {
						System.out.println("coord found=" + cs);
						dcs.remove(cs);
					}
				}

				if (exists == null) {
					int coordSysId = dasSourceManager.createCoordSys(conn,
							csNameId, categoryId, ncbiId, null);
					exists = dasSourceManager.getCoordinateSystem(conn,
							coordSysId);
					if (exists == null) {
						throw new RegistryException(
								"Unable to create coordinate system");
					}
				}
				createdDcs.add(exists);
				pingLastModified();

			}

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		return createdDcs;
	}

	/**
	 * returns all capabilites that are known to the registry service
	 * 
	 * @returns a String[] array contain all the know DAS/1 capabilities
	 * 
	 * 
	 * */
	public String[] getAllCapabilities() {

		// return Capabilities.getCommandStrings();
		return Capabilities.getCapabilityStrings();
	}

	/**
	 * general interface to search the registry :
	 * 
	 * @return all DasSources that match the arguments
	 */
	public List<DasSource> findService(String capability, String[] categories,
			String[] names, String[] organisms, String[] labels, String spec) {

		List<DasSource> localSources = new ArrayList<DasSource>();

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
			localSources = dasSourceManager.findService(conn, capability,
					categories, names, organisms, labels, prefix, spec);
		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return localSources;

	}

	public boolean checkPasskey(String serverurl, String passkey) {
		Connection conn = null;

		boolean ok = false;
		try {

			conn = dataSource.getConnection();
			ok = checkPasskey(conn, serverurl, passkey);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
		}

		return ok;
	}

	public boolean checkPasskey(Connection conn, String serverurl,
			String passkey) {
		// System.out.println("das registry sql checkPasskey");

		if (passkey.equals(""))
			return false;

		int testpasskey = -1;
		try {
			testpasskey = Integer.parseInt(passkey);
		} catch (java.lang.NumberFormatException e) {
			// System.out.println("wrong testpasskey!");
			return false;
		}
		int realpasskey = -129831298;
		try {
			realpasskey = dasSourceManager.getPasskey(conn, serverurl);
		} catch (SQLException e) {
			e.printStackTrace();
			// System.out.println("could not get realpasskey!");
			return false;
		}

		int masterkey = -1;
		boolean masterprovided = false;
		try {
			String masterpass = (String) configuration.get("passkey");
			if (masterpass != null) {
				masterkey = Integer.parseInt(masterpass);

				masterprovided = true;
			}
		} catch (java.lang.NumberFormatException e) {
			e.printStackTrace();
			// System.out.println("error getting masterkey!");
			// System.err.println("no configuration for passkey found in config file!");
			// return false;
		}

		if (realpasskey == testpasskey) {
			// System.out.println("keys match!");
			return true;
		}
		if (masterprovided) {
			if (masterkey == testpasskey) {
				// System.out.println("masterkey matches");
				return true;
			}
		}
		return false;

	}

	/**
	 * removes a service from the registry the key is the "password" only
	 * available to the server administrator an int is returned, wich provides
	 * the status of the request: 1: successfully removed 2: incorrect key ; 0:
	 * some other error occured;
	 */
	public int removeService(String serverurl, String passkey) {

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

		boolean keyOk = false;
		int status = 0;

		try {
			keyOk = checkPasskey(conn, serverurl, passkey);
			// System.out.println("remove Service");

			if (keyOk) {

				int auto_id = dasSourceManager.getAutoIdForURL(serverurl);

				dasSourceManager.removeLocalSource(auto_id);
				status = 1;

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		pingLastModified();

		if (!keyOk) {
			return WRONG_PASSKEY;
		} else {
			return status;
		}
	}

	// public int removeService(String url){
	// this.removeService(serverurl, passkey)
	// }

	public void postMail(String recipients[], String subject, String message,
			String from) throws MessagingException {
		boolean debug = false;

		// Set the host smtp address
		Properties props = new Properties();
		String sendmailhost = (String) configuration.get("sendmailhost");
		props.put("mail.smtp.host", sendmailhost);

		// create some properties and get the default Session
		Session session = Session.getDefaultInstance(props, null);
		// System.out.println(session.);
		session.setDebug(debug);

		// create a message
		Message msg = new MimeMessage(session);

		// set the from and to address
		InternetAddress addressFrom = new InternetAddress(from);
		msg.setFrom(addressFrom);

		InternetAddress[] addressTo = new InternetAddress[recipients.length];
		for (int i = 0; i < recipients.length; i++) {
			addressTo[i] = new InternetAddress(recipients[i]);
		}
		msg.setRecipients(Message.RecipientType.TO, addressTo);

		// Optional : You can also set your custom headers in the Email if you
		// Want
		// msg.addHeader("MyHeaderName", "myHeaderValue");

		// Setting the Subject and Content Type
		msg.setSubject(subject);
		msg.setContent(message, "text/html");
		Transport.send(msg);
	}

	/**
	 * send an email to the admin of server url, reminding him of his key
	 * required to delete a server from database To prevent flaming a key can
	 * only be sent once in a while. returns:
	 * 
	 * 1 : successfully sent mail 2 : if the time difference is not big enough,
	 * and no mail has been sent 3: some other error occured; 4: the email
	 * address specified in >sendmailto< does not match the one stored in the
	 * registry for this server.
	 */

	public int requestKey(String url, String sendmailto) {

		// DasSource ds = getSource(url);
		Connection conn = null;
		try {
			// debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return 3;
		}

		int passkey = 0;
		try {
			int canSend = checkCanSendMail(conn, url, sendmailto);
			if (canSend != 0) {
				// according to definition returns 2.
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
				return canSend;
			}
			passkey = dasSourceManager.getPasskey(conn, url);
			DasSource ds = dasSourceManager
					.getDasSourceByUrl(conn, url, prefix);
			String[] to = new String[1];
			to[0] = ds.getAdminemail();
			String message = " Dear DAS server administrator,\n\n";
			message += "the key for your server " + url + " is:\n\n";
			message += "    " + passkey + " \n";

			String serveradmin = (String) configuration.get("serveradmin");
			postMail(to, "email from DAS registry server", message, serveradmin);

		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.close();
			} catch (SQLException es) {
				es.printStackTrace();
			}
			return 3;
		}

		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 1;

	}

	/**
	 * watchdog functionality: notify server admins if their servers are down.
	 */
	public void sendAdminNotificationMails() {
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		try {
			DasCoordinateSystemCollector coordmap = dasSourceManager
					.getAllCoordinateSystemsMap(conn, prefix);

			// if all capabilities are not valid for this source and this has
			// been the case for more than two days send an email and put
			// warning sign on list servers

			String[] unhappyAdmins = dasSourceManager
					.getUnhappyServerAdmins(conn);

			Das1Validator validator = new Das1Validator();
			if (unhappyAdmins.length > 0) {
				System.out.println("sending notification emails to "
						+ unhappyAdmins.length + " admins");
			} else {
				System.out.println("no notification emails need to be sent");
			}

			List serversSent = new ArrayList();

			for (int i = 0; i < unhappyAdmins.length; i++) {

				String adminemail = unhappyAdmins[i];
				// System.out.println(adminemail);
				String msg = "Dear DAS - server administrator, \n\n";
				msg += " this message has been automatically created by the DAS - registration service, \n";
				msg += " http://www.dasregistry.org/";
				msg += " because you requested to be alerted if one or several of your DAS sources are \n";
				msg += " down for > 2 days.\n";
				msg += " It has not been possible to get any valid response for the following DAS source(s) for 2 days:\n\n";

				DasSource[] sources = dasSourceManager
						.getUnhappyDasSourcesForAdmin(conn, adminemail,
								coordmap, prefix, 2, 0);

				for (int d = 0; d < sources.length; d++) {
					DasSource ds = sources[d];
					System.out.println(ds.getNickname());

					msg += "DAS Source:\n" + "<pre> id:                 "
							+ ds.getId() + "\n" + " nickname:           "
							+ ds.getNickname() + "\n" + " url:                "
							+ ds.getUrl() + "\n" + " last time o.k.:     "
							+ ds.getLeaseDate() + "\n";

					msg += " For more info, please proceed here:\n"
							+ "<a href=\"http://www.dasregistry.org/showdetails.jsp?auto_id="
							+ ds.getId()
							+ "\">"
							+ " http://www.dasregistry.org/showdetails.jsp?auto_id="
							+ ds.getId() + "</a><br/>\n";
					msg += " you also might want to validate this DAS source at:\n"
							+ "<a href=\"http://www.dasregistry.org/validateServer.jsp?auto_id="
							+ ds.getId()
							+ "\">"
							+ "http://www.dasregistry.org/validateServer.jsp?auto_id="
							+ ds.getId() + "</a><br/>\n";
					msg += "<br/> =============\n\n";

					String url = ds.getUrl();
					if (!serversSent.contains(url)) {
						serversSent.add(url);
					}
				}

				// System.out.println(msg);

				String serveradmin = (String) configuration.get("serveradmin");
				String[] to = new String[2];

				to[0] = adminemail;
				to[1] = "dasregistry@sanger.ac.uk";
				String subject = " auto alert from DAS registration server - servers scheduled for deletion in "
						+ (DasRegistrySql.ARCHIVE_TIME - 2) + " days";
				try {
					postMail(to, subject, msg, serveradmin);
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}

			Iterator iter = serversSent.iterator();
			while (iter.hasNext()) {
				String url = (String) iter.next();

				// need to set status to 1 but for testing leave as 0
				dasSourceManager.unhappyAlertSent(conn, url, 1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	public void sendLastWeekWarningEmails() {

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		try {
			DasCoordinateSystemCollector coordmap = dasSourceManager
					.getAllCoordinateSystemsMap(conn, prefix);

			// if all capabilities are not valid for this source and this has
			// been the case for more than two days send an email and put
			// warning sign on list servers

			String[] unhappyAdmins = dasSourceManager
					.getLastWeekWarningServerAdmins(conn);

			Das1Validator validator = new Das1Validator();
			if (unhappyAdmins.length > 0) {
				System.out.println("sending notification emails to "
						+ unhappyAdmins.length + " admins");
			} else {
				System.out.println("no notification emails need to be sent");
			}

			List serversSent = new ArrayList();

			for (int i = 0; i < unhappyAdmins.length; i++) {

				String adminemail = unhappyAdmins[i];
				// System.out.println(adminemail);
				String msg = "Dear DAS - server administrator, \n\n";
				msg += " this message has been automatically created by the DAS - registration service, \n";
				msg += " http://www.dasregistry.org/";
				msg += " In 1 week your some of your sources will be deleted:\n\n";

				DasSource[] sources = dasSourceManager
						.getUnhappyDasSourcesForAdmin(conn, adminemail,
								coordmap, prefix, 53, 1);

				for (int d = 0; d < sources.length; d++) {
					DasSource ds = sources[d];
					System.out.println(ds.getNickname());

					msg += "DAS Source:\n" + "<pre> id:                 "
							+ ds.getId() + "\n" + " nickname:           "
							+ ds.getNickname() + "\n" + " url:                "
							+ ds.getUrl() + "\n" + " last time o.k.:     "
							+ ds.getLeaseDate() + "\n";

					msg += " For more info, please proceed here:\n"
							+ "<a href=\"http://www.dasregistry.org/showdetails.jsp?auto_id="
							+ ds.getId()
							+ "\">"
							+ " http://www.dasregistry.org/showdetails.jsp?auto_id="
							+ ds.getId() + "</a><br/>\n";
					msg += " you also might want to validate this DAS source at:\n"
							+ "<a href=\"http://www.dasregistry.org/validateServer.jsp?auto_id="
							+ ds.getId()
							+ "\">"
							+ "http://www.dasregistry.org/validateServer.jsp?auto_id="
							+ ds.getId() + "</a><br/>\n";
					msg += "<br/> =============\n\n";

					String url = ds.getUrl();
					if (!serversSent.contains(url)) {
						serversSent.add(url);
					}
				}

				// System.out.println(msg);

				String serveradmin = (String) configuration.get("serveradmin");
				String[] to = new String[2];

				to[0] = adminemail;
				to[1] = "dasregistry@sanger.ac.uk";
				String subject = " auto alert from DAS registration server - servers scheduled for deletion in "
						+ (DasRegistrySql.ARCHIVE_TIME - 7) + " days";
				try {
					postMail(to, subject, msg, serveradmin);
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}

			Iterator iter = serversSent.iterator();
			while (iter.hasNext()) {
				String url = (String) iter.next();

				// need to set status to 1 but for testing leave as 0
				dasSourceManager.unhappyAlertSent(conn, url, 2);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * returns 0 if a reminder Mail can be sent, 2 if reminder has been sent
	 * resentyl, 4 if admin email does not match, otherwise 3
	 * 
	 * @see {requestKey()}
	 * */
	protected int checkCanSendMail(Connection conn, String url,
			String sendmailto) throws SQLException {

		DasSource ds = dasSourceManager.getDasSourceByUrl(conn, url, prefix);

		// make sure that the admin email addresses fit.
		if (!ds.getAdminemail().equals(sendmailto))
			return 4;

		// java.util.Date reminderDate = ds.getLeaseDate();
		Timestamp reminderDate = dasSourceManager.getReminderDate(conn, url);

		if (reminderDate == null) {
			dasSourceManager.setReminderDate(conn, url);
			return 0;
		}
		System.out.println("got reminder date " + reminderDate.getTime());

		java.util.Date now = new java.util.Date();
		long remindertime = reminderDate.getTime();
		long nowtime = now.getTime();
		long diff = nowtime - remindertime;
		System.out.println(" now " + now + " " + (nowtime / 1000)
				+ " reminder " + reminderDate + " " + (remindertime / 1000)
				+ " diff " + (diff / 1000) + "config diff:"
				+ (config_timediff / 1000));

		if (diff > config_timediff) {
			System.out.println("o.k. to send mail");
			dasSourceManager.setReminderDate(conn, url);
			return 0;
		}

		System.out.println("mail just has been sent");
		return 2;
	}

	/**
	 * renew Lease for server an int is returned, wich provides the status of
	 * the request: 1: successfully renewed 0: some other error occured;
	 */
	public int renewLease(String serverurl, boolean status) {

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		try {
			// new version: everybody can renew the lease of a server ...
			// there is nothing dangerous going on here ..

			// renew lease
			dasSourceManager.renewLeaseNow(conn, serverurl);
			// if there are too many logs / day the clean-servlet will take care
			// of it
			// after 2 days...
			dasSourceManager.logStatus(conn, serverurl, status);

			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return 1;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.close();
			} catch (SQLException es) {
				es.printStackTrace();
			}
			return 0;
		}
	}

	/** return all available Organisms in database */
	public String[] getOrganismsByName() {
		// System.out.println("DasRegistrySql - get organism by name");
		String[] orgs = new String[0];

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			orgs = dasSourceManager.getOrganismsByName(conn);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return orgs;

	}

	/** get available categories */
	public String[] getCoordSysTypes() {
		String[] categories = new String[0];
		Connection conn = null;

		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			categories = dasSourceManager.getCoordSysTypes(conn);

		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return categories;
	}

	public String[] getCoordSysByType(String type) {

		String[] coordst = new String[0];
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
			coordst = dasSourceManager.getCoordSysByType(conn, type);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return coordst;
	}

	public static String getPrefix(String dassourceId) throws RegistryException {
		String[] spl = dassourceId.split(PREFIXSPLIT);
		if (spl.length < 2) {
			throw new RegistryException("das source id >" + dassourceId
					+ "< does not contain a " + PREFIXSPLIT);
		}
		return spl[0];
	}

	/**
	 * test if the das source has the prefix of the local registry
	 * 
	 * @param dassourceId
	 * @return a flag
	 * @throws RegistryException
	 */
	public boolean hasLocalPrefix(String dassourceId) throws RegistryException {
		// System.out.println("is local prefix? "+dassourceId + " == " + prefix
		// + "?") ;
		String thisprefix = getPrefix(dassourceId);
		if (thisprefix.equals(prefix)) {
			// System.out.println("yes");
			return true;
		}
		// System.out.println("no");
		return false;

	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public DasSource getDasSourceByURL(String dassourceurl) {
		// System.out.println("getting by URL " + dassourceurl);
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			System.err.println("could not get DB Connection ...");
			e.printStackTrace();

			DasSource source = new Das1Source();
			return source;
		}

		DasSource ds = new Das1Source();

		try {

			ds = dasSourceManager.getDasSourceByUrl(conn, dassourceurl, prefix);

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		// System.out.println("got DAS source by URL " + ds);
		// System.out.println("an error occured - returning empty das source");
		return ds;

	}

	public DasSource getDasSource(String dassourceId) {
		// System.out.println("getting DAS source " +dassourceId);



		// System.out.println("getting data from local registry");

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			System.err.println("could not get DB Connection ...");
			e.printStackTrace();

			DasSource source = new Das1Source();
			return source;
		}

		DasSource ds = new Das1Source();

		try {

			ds = dasSourceManager.getDasSourceById(conn, dassourceId, prefix);

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		// System.out.println("an error occured - returning empty das source");
		return ds;
	}

	/**
	 * write a log into the db that this das source is now() either ok or not
	 * 
	 * @param ds
	 * @param status
	 */

	public void logSource(DasSource ds, boolean status) {

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			dasSourceManager.logStatus(conn, ds.getUrl(), status);

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}
	}

	/**
	 * try to validate a server, returns valid capabilities in an array
	 * 
	 */
	/*
	 * public DasValidationResult validate(String spec, String url, String
	 * testcode, String[] capabilities, String ontology, String auto_id) {
	 * System.out.println("running validation from DasRegistrySql");
	 * 
	 * DasCoordinateSystem dcs = new DasCoordinateSystem();
	 * dcs.setTestCode(testcode); DasCoordinateSystem[] dcsses = new
	 * DasCoordinateSystem[1]; dcsses[0] = dcs; boolean validateOntology =
	 * false; if (ontology.equals("yes") || ontology.equals("true") ||
	 * ontology.equals("on")) { validateOntology = true; } Validator
	 * validator=new Validator(); boolean updateDatabase=true;
	 * 
	 * DasValidationResult result=validator.validate(spec, url, capabilities,
	 * dcsses, validateOntology, updateDatabase, this, auto_id);
	 * 
	 * return result; //return validationInRegistry(spec, url, capabilities,
	 * dcsses,validateOntology);
	 * 
	 * }
	 */

	/** return which errors have been produced during validation... */
	public String getValidationMessage() {
		return validationMessage;
	}

	public String[] getOrganismsByName(String startOfName) {
		String[] orgs = new String[0];

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			orgs = dasSourceManager.getOrganismsByName(startOfName, conn);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return orgs;

	}

	public List<DasCoordinateSystem> getCoordSysByName(String startOfName)
			throws Exception {

		// System.out.println("in getAllCoordinateSystems in DasRegistrySql.java");
		ArrayList coords = new ArrayList();

		// DasCoordinateSystem[] knownCoords = new DasCoordinateSystem[0];
		// if (!cacheParent) {
		// System.out.println("DasRegistrySql: ! cacheParent");
		// /* know sources part --- */
		// DasRegistry dasregistryaxisclient = getParentDasRegistry();
		// if (dasregistryaxisclient != null)
		// knownCoords = dasregistryaxisclient.getAllCoordinateSystems();
		//
		// // ArrayList sources = new ArrayList() ;
		// for (int i = 0; i < knownCoords.length; i++)
		// coords.add(knownCoords[i]);
		// }

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();

			DasCoordinateSystem[] localCoords = getLocalCoordinateSystemsByName(
					startOfName, prefix, conn);

			for (int l = 0; l < localCoords.length; l++) {
				coords.add(localCoords[l]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException es) {
					es.printStackTrace();
				}
			}
		}

		return coords;
		// DasCoordinateSystem[] coordinates=null;
		//
		// Connection conn = null;
		// try {
		// debug(dataSource);
		// conn = dataSource.getConnection();
		//
		// coordinates = dao.getCoordinatesByName(startOfName, conn);
		//
		// } catch (SQLException e) {
		// e.printStackTrace();
		// } finally {
		//
		// if (conn != null) {
		// try {
		// conn.close();
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }
		// }
		// }
		//
		// return coordinates;

	}

	public int updateTypes(List<Map<String, String>> types, DasSource ds) {
		Connection conn = null;
		Connection connForTypes = null;
		int status = 13;

		try {

			conn = dataSource.getConnection();
			connForTypes = dataSource.getConnection();

			int UNK_AUTO_ID = DasSourceManager.UNKNOWN_AUTO_ID;
			int auto_id = UNK_AUTO_ID;
			try {
				auto_id = dasSourceManager.getAutoIdForURL(ds.getUrl());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException ex) {
						ex.printStackTrace();
					}
				}
			}

			if (auto_id == UNK_AUTO_ID) {
				logger.info("trying to update type data in a source that is not known - something wrong with AutoValidation");
				// try {
				// if (hasLocalPrefix(ds.getId())) {
				// String[] spl = ds.getId().split(PREFIXSPLIT);
				// if (spl.length == 2) {
				// try {
				// auto_id = Integer.parseInt(spl[1]);
				// } catch (NumberFormatException ex) {
				// ex.printStackTrace();
				// }
				// }
				// }
				// } catch (RegistryException rex) {
				// rex.printStackTrace();
				// }
			}

			if (auto_id != UNK_AUTO_ID) {
				boolean success = dasSourceManager.updateTypesStatusInDb(
						connForTypes, auto_id, ds, types);

				// UserManager um = new UserManager();
				// um.addDasSource2Owner(auto_id);

				if (success)
					status = 1;

			} else {
				System.err
						.println("could not find auto_id for requested DasSource "
								+ ds.getUrl());
			}
		} catch (SQLException e) {
			// System.out.println(e.getMessage());
			e.printStackTrace();
			status = DATABASE_ERROR;
		} finally {
			if (connForTypes != null) {
				try {
					connForTypes.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		// pingLastModified();

		return status;
	}

	public List<Map<String, String>> getTypes(String auto_idString) {
		List<Map<String, String>> types = null;
		Connection conn = null;

		try {

			conn = dataSource.getConnection();
			// System.out.println("auto_id="+auto_idString);
			if (auto_idString.startsWith("DS_")) {
				auto_idString = auto_idString.substring(3,
						auto_idString.length());
			}
			int auto_id = Integer.parseInt(auto_idString);

			types = dasSourceManager.getTypesFromDb(conn, auto_id);

		} catch (SQLException e) {
			// System.out.println(e.getMessage());
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return types;
	}

	/*
	 * private void simple(){ Connection conn = null ; try {
	 * 
	 * conn = dataSource.getConnection() ; } catch (SQLException e) {
	 * e.printStackTrace(); } finally { if ( conn != null){ try { conn.close();
	 * } catch (SQLException ex){ ex.printStackTrace(); } } }
	 * 
	 * 
	 * }
	 */

	public int updateSpecification(String spec, DasSource ds) {

		Connection conn = null;
		Connection connForTypes = null;
		int status = 13;

		try {

			conn = dataSource.getConnection();
			connForTypes = dataSource.getConnection();
			int UNK_AUTO_ID = DasSourceManager.UNKNOWN_AUTO_ID;
			int auto_id = UNK_AUTO_ID;
			try {
				auto_id = dasSourceManager.getAutoIdForURL(ds.getUrl());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				if (connForTypes != null) {
					try {
						connForTypes.close();
					} catch (SQLException ex) {
						ex.printStackTrace();
					}
				}
			}

			if (auto_id == UNK_AUTO_ID) {
				logger.info("trying to validate a data source that is not known - something wrong with AutoValidation");
				// try {
				// if (hasLocalPrefix(ds.getId())) {
				// String[] spl = ds.getId().split(PREFIXSPLIT);
				// if (spl.length == 2) {
				// try {
				// auto_id = Integer.parseInt(spl[1]);
				// } catch (NumberFormatException ex) {
				// ex.printStackTrace();
				// }
				// }
				// }
				// } catch (RegistryException rex) {
				// rex.printStackTrace();
				// }
			}

			if (auto_id != UNK_AUTO_ID) {
				boolean success = dasSourceManager
						.updateSpecificationStatusInDb(conn, auto_id, spec);

				// UserManager um = new UserManager();
				// um.addDasSource2Owner(auto_id);

				if (success)
					status = 1;

			} else {
				System.err
						.println("could not find auto_id for requested DasSource "
								+ ds.getUrl());
			}
		} catch (SQLException e) {
			// System.out.println(e.getMessage());
			e.printStackTrace();
			status = DATABASE_ERROR;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		// pingLastModified();

		return status;
	}

	public void sendAdminDeletionNotificationMails() {

		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return;
		}
		try {
			DasCoordinateSystemCollector coordmap = dasSourceManager
					.getAllCoordinateSystemsMap(conn, prefix);

			String[] unhappyAdmins = dasSourceManager
					.getUnhappyDeletingSourceServerAdmins(conn);

			Das1Validator validator = new Das1Validator();
			if (unhappyAdmins.length > 0) {
				System.out.println("sending deletion emails to "
						+ unhappyAdmins.length + " admins");
			} else {
				System.out.println("no notification emails need to be sent");
			}

			List serversSent = new ArrayList();

			for (int i = 0; i < unhappyAdmins.length; i++) {

				String adminemail = unhappyAdmins[i];
				System.out.println(adminemail);
				String msg = "Dear DAS - server administrator, \n\n";
				msg += " this message has been automatically created by the DAS - registration service, \n";
				msg += " http://www.dasregistry.org/";
				msg += " It has not been possible to get any valid response from the following DAS source(s) for a while and so they have been removed:\n\n";

				DasSource[] sources = dasSourceManager
						.getUnhappyDasSourcesForAdmin(conn, adminemail,
								coordmap, prefix, DasRegistrySql.ARCHIVE_TIME,
								2);

				for (int d = 0; d < sources.length; d++) {
					DasSource ds = sources[d];
					System.out.println("nickname=" + ds.getNickname());

					msg += "DAS Source:\n" + "<pre> id:                 "
							+ ds.getId() + "\n" + " nickname:           "
							+ ds.getNickname() + "\n" + " url:                "
							+ ds.getUrl() + "\n" + " last time o.k.:     "
							+ ds.getLeaseDate() + "\n" + "</pre>" + "\n";
					// + result.getValidationResultsString() + "<br/><br/>\n\n";

					;

					msg += " For more info, please proceed here:\n"
							+ "<a href=\"http://www.dasregistry.org/showdetails.jsp?auto_id="
							+ ds.getId()
							+ "\">"
							+ " http://www.dasregistry.org/showdetails.jsp?auto_id="
							+ ds.getId() + "</a><br/>\n";
					msg += " you also might want to validate this DAS source at:\n"
							+ "<a href=\"http://www.dasregistry.org/validateServer.jsp?auto_id="
							+ ds.getId()
							+ "\">"
							+ "http://www.dasregistry.org/validateServer.jsp?auto_id="
							+ ds.getId() + "</a><br/>\n";
					msg += "<br/> =============\n\n";

					String url = ds.getUrl();
					if (!serversSent.contains(url)) {
						serversSent.add(url);
					}
				}

				System.out.println(msg);

				String serveradmin = (String) configuration.get("serveradmin");
				String[] to = new String[2];

				to[0] = adminemail;// "jw12@sanger.ac.uk";
				to[1] = "dasregistry@sanger.ac.uk";
				String subject = "Your DAS source has been deleted from the registry";
				try {
					postMail(to, subject, msg, serveradmin);
				} catch (MessagingException e) {
					e.printStackTrace();
				}
			}

			Iterator iter = serversSent.iterator();
			while (iter.hasNext()) {
				String url = (String) iter.next();
				dasSourceManager.unhappyAlertSent(conn, url, 3);// set to 2 as
				// server has
				// been archived
				// and removed
				// from main
				// registry
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * method to get a list of servers to archive and delete from main registry
	 * 
	 * @param sourcesForArchiving
	 * @return return 0 for unsuccesful 1 for succesful
	 */
	public boolean archiveServers(List<DasSource> sourcesForArchiving) {
		boolean success = true;
		DataSource dataSource = getDataSource();
		Connection conn = null;
		// boolean doValidate = false;
		String errors = "";
		try {
			conn = dataSource.getConnection();

			for (DasSource source : sourcesForArchiving) {
				String serverUrl = source.getUrl();
				System.out.println(serverUrl);
				// all this does now is change active in the registry table to 0
				// rather than 1
				errors += dasSourceManager.archiveSource(serverUrl);
				System.out.println(source.getAdminemail());
				System.out.println("archived url=" + serverUrl);
				String serverAdmin = (String) configuration.get("serveradmin");
				this.deletionEmailForServerControlled(serverAdmin, serverUrl);
			}
			// as we now update the valid_capabilities tables we want
			// to tell the sources.xml returned by the registry it will need
			// updating etc
			dasSourceManager.pingLastModified(conn);
			if (errors.equals("")) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return success;
	}

	private void deletionEmailForServerControlled(String serverAdmin,
			String serverUrl) {
		String[] emailList = new String[1];
		emailList[0] = serverAdmin;
		try {
			postMail(emailList, "source Deleted from registry " + serverUrl,
					"", serverAdmin);
		} catch (MessagingException e) {
			e.printStackTrace();
		}

	}

	/**
 * 
 */
	public String storeServer(String sourcesPath, String openId,
			String description) throws SQLException {

		String errors = serverManager.storeServer(sourcesPath, openId,
				description);

		String serverUrl = Das1Validator
				.removeDataSourceNameFromUrl(sourcesPath);
		DasServer newServer = serverManager.getServerForServerUrl(serverUrl);

		// we need to mirror the sources from this sources document updating any
		// the registry table has already and
		// adding any we don't have already

		if (errors.equals("") && newServer != null) {
			mirror.mirrorServer(newServer);
		}
		// then we want to add the serverId to all the sources we have that are
		// from this server
		// List<DasSource>
		// sources=listServicesByServer(newServer.getServer_id());
		// dsm.addServerIdToSources(sources, serverId);

		return errors;

	}

	/**
	 * get a list of sources for the server url
	 * 
	 * @param serverUrl
	 * @return
	 */
	public List<DasSource> listServicesByServer(int serverId) {

		List<DasSource> sources = new ArrayList<DasSource>();
		Connection conn = null;
		try {
			debug(dataSource);
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			// System.err.println("could not get DB Connection ...");
			logger.warning("DasRegistrySql listServices: could not open DB connection!"
					+ e.getMessage());
			e.printStackTrace();
		}
		boolean allOk = true;
		try {

			// int size = dao.getNumberSources(conn);
			// System.out.println("got nr sources:" + size);
			// String[][] sources = new String[size][];
			// sources = new DasSource[size + knowSourcesSize];

			sources = dasSourceManager.getAllSourcesForServer(conn, prefix,
					serverId);
			// System.out.println("after getAllSources");
			// for (int i = 0; i < registeredSources.size(); i++) {
			// sources[i + knowSourcesSize] = registeredSources.get(i);
			// }
			return sources;

		} catch (Exception e) {
			e.printStackTrace();
			allOk = false;
		} finally {
			try {
				conn.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return sources;
		// if (allOk)
		// return sources;
		// else {
		// return new DasSource[0];
		// }
	}

	public List<DasSource> getDasSourcesForServerURL(String serverRootUrl) {
		List<DasSource> sourcesForServer = null;

		DasServer server = serverManager.getServerIdForServerUrl(serverRootUrl);
		sourcesForServer = this.listServicesByServer(server.getServerId());

		return sourcesForServer;
	}

	public List<DasServer> listServers() {
		List<DasServer> servers = serverManager.getDasServers();
		return servers;
	}

	public void updateTimes(Map<Capabilities, Long> times, DasSource ds) {
		int auto_id = dasSourceManager.getAutoIdForURL(ds.getUrl());
		System.out.println("auto_id=" + auto_id);
		dasSourceManager.updateTimesStatusInDb(auto_id, ds, times);
	}

	public int removeService(String serverurl, RegistryUser user) {
		// TODO Auto-generated method stub
		return 0;
	}

}
